import java.sql.*;

/** This is the class that controlls the connection to the database */
/** and executing the sql commands */
public class Database {

	private String userName = "javaUser";
	private String password = "test";
	private String url = "jdbc:mysql://127.0.0.1:3306/";
	private String dbName = "2340db";
	private String driver = "com.mysql.jdbc.Driver";
	private static Connection connection;
	private static Statement stmt;
	private static ResultSet rs;
	private static Database db = new Database();
	// Logger
	private static final Logger LOG = new Logger.getLogger("Database");

	/*
	 * Hey Brian, Database should be a singleton class, no need for
	 * constructors. query function should be static. ~ Hayson
	 */
	private Database() {
	}

	public static Database getInstance() {
		return db;
	}

	/** This class creates a new connection to the database */
	private void makeConnection() {
		try {
			Class Driv;
			try {
				Driv = Class.forName(driver);
			} catch (Exception e) {
				throw new Exception("Driver not found");
			}
			Driv.newInstance();
			connection = DriverManager.getConnection(url + dbName, userName,
					password);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	/** This class closes a database connection */
	private void closeConnection() {
		try {
			connection.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	// used with sql INSERT, UPDATE, DELETE statements
	// returns the number of rows affected by the sql statement
	public static int query(String qry) {
		db.makeConnection();
		int rows = 0;
		try {
			stmt = connection.createStatement();
			rows = stmt.executeUpdate(qry);
		} catch (Exception e) {
			LOG.severe("An SQL error occured in the statement:\n" + qry + "\n");
			return 0;
		}
		db.closeConnection();
		LOG.info("Query successful:\n" + qry + "\n");
		return rows;
	}

	// Used with sql SELECT statement
	public static ResultSet selectQuery(String qry) {
		db.makeConnection();
		try {
			stmt = connection.createStatement();
			rs = stmt.executeQuery(qry);
		} catch (Exception e) {
			LOG.severe("An SQL error occured in the statement:\n" + qry + "\n");
			e.printStackTrace();
		}
		db.closeConnection();
		LOG.info("Query successful:\n" + qry + "\n");
		return rs;
	}

	public static void main(String[] args) {
		// make a test query
		String sql = "INSERT INTO Users (username, encrypted_password, access_level, suspended) VALUES ('Patient', 'test', 1, 0)";
		String qry = "DELETE FROM Users WHERE username='Patient'";
		int row = query(qry);
		System.out.println("Number of rows affected = " + row);
	}
}
